from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_, text, join
from sqlalchemy.sql import Select
from typing import List, Optional, Dict, Any
from module_admin.entity.do.employee_social_security_do import EmployeeSocialSecurity
from module_admin.entity.do.employee_do import EmployeeInfo
from module_admin.entity.vo.employee_social_security_vo import EmployeeSocialSecurityQueryModel, EmployeeSocialSecurityModel
from utils.page_util import PageUtil
from utils.common_util import SqlalchemyUtil


class EmployeeSocialSecurityDao:
    """
    员工社保模块数据访问层
    """

    @classmethod
    async def get_employee_social_security_list(cls, db: AsyncSession, security_query: EmployeeSocialSecurityQueryModel, is_page: bool = True):
        """
        获取员工社保列表信息

        :param db: orm对象
        :param security_query: 查询参数对象
        :param is_page: 是否开启分页
        :return: 员工社保列表信息对象
        """
        # 构建基础查询
        query = select(
            *EmployeeSocialSecurity.__table__.columns,
            EmployeeInfo.name.label('employee_name'),  # 确保这里是 'employee_name'
        ).join(
            EmployeeInfo,
            EmployeeSocialSecurity.employee_id == EmployeeInfo.id
        ).where(
            EmployeeSocialSecurity.del_flag == '0',
            EmployeeSocialSecurity.employee_id == security_query.employee_id if security_query.employee_id else True,
            EmployeeSocialSecurity.start_date == security_query.start_date if security_query.start_date else True,
            EmployeeSocialSecurity.end_date == security_query.end_date if security_query.end_date else True,
        ).order_by(EmployeeSocialSecurity.id).distinct()

        raw_paginated_result = await PageUtil.paginate(db, query, security_query.page_num, security_query.page_size, is_page)

        return raw_paginated_result

    @classmethod
    async def get_employee_social_security(cls, db: AsyncSession, security_id: int):
        """
        获取单个员工社保详情
        :param db: orm对象
        :param security_id: 社保ID
        :return: 员工社保详情
        """
        stmt = select(
            *EmployeeSocialSecurity.__table__.columns,
            EmployeeInfo.name.label('employee_name'),  # 确保这里是 'employee_name'
        ).join(
            EmployeeInfo,
            EmployeeSocialSecurity.employee_id == EmployeeInfo.id
        ).where(
            and_(
                EmployeeSocialSecurity.id == security_id,
                EmployeeSocialSecurity.del_flag == '0'
            )
        )
        result = await db.execute(stmt)
        row = result.one_or_none()
        if row:
            # 直接将 SQLAlchemy Row 对象转换为 Pydantic 模型
            return EmployeeSocialSecurityModel(**SqlalchemyUtil.serialize_result(row, transform_case='snake_to_camel'))
        return None

    @classmethod
    async def add_employee_social_security(cls, db: AsyncSession, security: EmployeeSocialSecurityQueryModel):
        """
        新增员工社保信息
        :param db: orm对象
        :param security: 新增社保对象
        :return: 新增结果
        """
        security_dict = {
            'employee_id': security.employee_id,
            'is_paying': security.is_paying,
            'social_security_amount': security.social_security_amount,
            'start_date': security.start_date,
            'end_date': security.end_date,
            'del_flag': '0',
            'create_by': security.create_by,
            'create_time': security.create_time,
            'update_by': security.update_by,
            'update_time': security.update_time,
        }
        db_security = EmployeeSocialSecurity(**security_dict)
        db.add(db_security)
        await db.flush()
        return db_security

    @classmethod
    async def update_employee_social_security(cls, db: AsyncSession, security_id: int, security: EmployeeSocialSecurityQueryModel):
        """
        修改员工社保信息
        :param db: orm对象
        :param security_id: 社保ID
        :param security: 修改社保对象
        :return: 修改结果
        """
        security_dict = {
            'employee_id': security.employee_id,
            'is_paying': security.is_paying,
            'social_security_amount': security.social_security_amount,
            'start_date': security.start_date,
            'end_date': security.end_date,
            'update_by': security.update_by,
            'update_time': security.update_time,
        }
        stmt = select(EmployeeSocialSecurity).where(
            and_(
                EmployeeSocialSecurity.id == security_id,
                EmployeeSocialSecurity.del_flag == '0'
            )
        )
        result = await db.execute(stmt)
        security_obj = result.scalar_one_or_none()
        
        if security_obj:
            for key, value in security_dict.items():
                if value is not None:
                    setattr(security_obj, key, value)
            await db.flush()
            return True
        return False

    @classmethod
    async def delete_employee_social_security(cls, db: AsyncSession, security_id: int):
        """
        删除员工社保信息
        :param db: orm对象
        :param security_id: 社保ID
        :return: 删除结果
        """
        stmt = select(EmployeeSocialSecurity).where(
            and_(
                EmployeeSocialSecurity.id == security_id,
                EmployeeSocialSecurity.del_flag == '0'
            )
        )
        result = await db.execute(stmt)
        security_obj = result.scalar_one_or_none()
        
        if security_obj:
            security_obj.del_flag = '1'
            await db.flush()
            return True
        return False 